<?php

defined('IN_MY_PHP') || die(0);
// 初始化本文件单独使用时涉及到的常量
defined('CRLF') || define('CRLF', "\r\n");

/**
 * A PDOMySQLDriver for MySQL which convert PDOException to E_USER_ERROR
 * @author  netmou(leiyanfo@sina.com).
 * @since   2015/01/07.
 * @link    https://gitee.com/netmou/php-library
 * 1. Do multi-query like the follow one:
 * $stmt = $pdo->query('select * from `A`;select * from `B`');
 * You can fetch next sql-data-set by using '$stmt->nextRowset()', a sample no-meaning loop like the follow code:
 * do {
 *    while($stmt->fetchAll());
 * } while ($stmt->nextRowset());
 * 2. To avoid the error likes 'Cannot execute queries while other unbuffered queries are active', you should do the follow step before execute next queries:
 * $stmt->closeCursor();
 * 3. The automatic rollback only happens if you initiate the transaction via PDO::beginTransaction(). If you manually issue a query that begins a transaction PDO has no way of knowing about it and thus cannot roll it back if something bad happens.
 */
class PDOMySQLDriver extends PDO
{

	private $config = array(
		'host'=>MySQL_DB_HOST,
		'dbname'=>MySQL_DB_NAME,
		'port'=>MySQL_DB_PORT,
		'user'=>MySQL_DB_USER,
		'pwd'=>MySQL_DB_PWD,
	);

	//默认返回数据方式
	private $fetchMode = parent::FETCH_ASSOC;

	//表结构缓存
	private $tablePRICache = array();
	private $tableUNICache = array();
	private $tableColumnCache = array();
	private $lastQuerySQL = null;

	//请注意dsn中也能有字符集的设置
	private $options = array(
		parent::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8',
		parent::ATTR_PERSISTENT => false,
		parent::ATTR_ERRMODE => parent::ERRMODE_EXCEPTION,
	);


	/**
	 * PDOMySQLDriver 构造函数
	 * dsn-ref http://php.net/manual/en/ref.pdo-mysql.connection.php
	 * @return array $config=[host ,dbname, port, user, pwd]
	 * @return void
	 */
	public function __construct($config = array())
	{
		foreach($config as $key => &$val){
			if(array_key_exists($key, $this->config)){
				$this->config[$key] = $val;
			}
		}
		$dsn = "mysql:host={$this->config['host']};port={$this->config['port']};dbname={$this->config['dbname']}";
		parent::__construct($dsn, $this->config['user'], $this->config['pwd'], $this->options);
		$this->exec('SET sql_mode=\'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE\'');
		$this->exec('SET SESSION GROUP_CONCAT_MAX_LEN = 4096');
	}

	/**
	 * 获取只读的配置项：host
	 * @return string
	 */
	public function getHost()
	{
		return $this->config['host'];
	}

	/**
	 * 获取只读的配置项：dbname
	 * @return string
	 */
	public function getDBName()
	{
		return $this->config['dbname'];
	}

	/**
	 * 获取只读的配置项：port
	 * @return string
	 */
	public function getPort()
	{
		return $this->config['port'];
	}

	/**
	 * 获取最近实际执行的SQL语句
	 * @return string
	 */
	public function getLastQuerySQL()
	{
		return $this->lastQuerySQL;
	}

	/**
	 * 格式化字段、表名等标识符
	 * @return string
	 */
	private function formatKeyWord($keyword)
	{
		$keyword = trim($keyword);
		$keyword = str_replace('`', '', $keyword);
		return '`' . str_replace('.', '`.`', $keyword) . '`';
	}

	/**
	 * 处理空值以匹配字段类型
	 * @param string $columnVal
	 * @param array $columnInfo
	 * @return string
	 */
	private function filterData($columnVal, $columnInfo){
		if ($columnVal === '' && 0 == preg_match('/char|text|lob/i', $columnInfo['Type'])) {
			$columnVal = ($columnInfo['Null'] == 'YES')? null : $columnInfo['Default'];
		}

		return $columnVal;
	}

	/**
	 * 给（非信任的）数据进行转义并加边界标识
	 * 处理NULL和''的冲突(将'NULL'字符串转换成'\NULL'，null会转为'NULL')
	 * 原生的quote的方法对null和空字符串处理并不是很友好
	 * @param string $data
	 * @return string
	 */
	public function enc($data)
	{
		
		if($data == 'NULL'){

			return '{^\\NULL$}';
		}
		if($data === null){

			return '{^NULL$}';
		}
		if(!is_scalar($data)){
			trigger_error("数据‘{$data}’必须是标量！", E_USER_ERROR);
		}

		return '{^' . str_replace(array('\\', '{^', '$}'), array('\\\\', '\\{\\^', '\\$\\}'), $data). '$}';
	}

	/**
	 * 给数据（非信任）去转义
	 * @param string $data
	 * @return string
	 */
	private function dec(&$data)
	{
		if($data == '\\NULL'){
			
			return 'NULL';
		}
		if($data == 'NULL'){
			
			return null;
		}
		return str_replace(array('\\{\\^', '\\$\\}', '\\\\'), array('{^', '$}', '\\'), $data);
	}

	/**
	 * 设置返回数据的形式
	 * @param int $mode
	 * @return void
	 */
	public function setFetchMode($mode)
	{
		$this->fetchMode = $mode;
	}

	/**
	 * 通过表名获取表结构(不缓存复合主键).
	 *
	 * @param string $tableName
	 * @return void
	 */
	private function tableStruct($tableName)
	{
		$formatedTableName = $this->formatKeyWord($tableName);
		$columnSet = $this->fetchData("SHOW COLUMNS FROM {$formatedTableName}", parent::FETCH_ASSOC);
		$primaryColumnCount = 0;
		$UNIKeySet = array();
		foreach ($columnSet as &$column) {
			$upcase_colname = strtoupper($column['Field']);
			$this->tableColumnCache[$tableName][$upcase_colname] = $column;
			if ($column['Key'] == 'PRI') {
				$this->tablePRICache[$tableName] = $column['Field'];
				++$primaryColumnCount;
			}
			if ($column['Key'] == 'UNI') {
				$UNIKeySet[] = $column['Field'];
			}
		}
		$this->tableUNICache[$tableName] = $UNIKeySet;
		if ($primaryColumnCount != 1) {
			$this->tablePRICache[$tableName] = null;
		}
	}

	/**
	 * 执行预查询操作（重写改变异常消息）
	 * @param string $prepare
	 * @param array $option
	 * @return object PDOStatement
	 */
	public function prepare($prepare, $option = array())
	{
		try {
			$this->lastQuerySQL = $prepare;

			return  parent::prepare($prepare, $option);
		} catch (PDOException $e) {
			trigger_error(CRLF."-> ".$e->getMessage() . CRLF . "-> On Prepare SQL: " . $prepare, E_USER_ERROR);
		}
	}

	/**
	 * 执行查询操作（重写来支持数据模版语法并改变异常消息）
	 * @param string $query
	 * @param int $fetchMode
	 * @return object PDOStatement
	 */
	public function query($query, $fetchMode = null){
		try{
			$parseResult = array();
			if (preg_match_all('/\\{\\^((?:(?R)|(?!\\{\\^)(?!\\$\\}).)*)\\$\\}/s', $query, $matches)) {
				for ($i = 0; $i<count($matches[0]); $i++) {
					$key = 'key' . $i;
					$val = $this->dec($matches[1][$i]);
					$parseResult[$key] = $val;
					$pos = strpos($query, $matches[0][$i]);
					$query = substr_replace($query, ':'.$key, $pos, strlen($matches[0][$i]));
				}
			}
			if(count($parseResult) > 0){
				$stmt = $this->prepare($query);
				$this->bindAssocArrayValue($stmt, $parseResult);
				$stmt->execute();
			}else{
				$stmt = parent::query($query);
			}
			$stmt->setFetchMode($fetchMode != null ? $fetchMode : $this->fetchMode);

			return $stmt;
		} catch (PDOException $e) {
			trigger_error(CRLF . "-> ".$e->getMessage() . CRLF . "-> On Query SQL: ".$query, E_USER_ERROR);
		}
	}

	/**
	 * 执行非查询类操作（重写改变异常消息）
	 * @param string $query
	 * @return mixed
	 */
	public function exec($query)
	{
		try{
			$parseResult = array();
			if (preg_match_all('/\\{\\^((?:(?R)|(?!\\{\\^)(?!\\$\\}).)*)\\$\\}/s', $query, $matches)) {
				for ($i = 0; $i<count($matches[0]); $i++) {
					$key = 'key' . $i;
					$val = $this->dec($matches[1][$i]);
					$parseResult[$key] = $val;
					$pos = strpos($query, $matches[0][$i]);
					$query = substr_replace($query, ':'.$key, $pos, strlen($matches[0][$i]));
				}
			}
			if(count($parseResult) > 0){
				$stmt = $this->prepare($query);
				$this->bindAssocArrayValue($stmt, $parseResult);
				$stmt->execute();

				return $stmt->rowCount();
			}else{

				return parent::exec($query);
			}
		} catch (PDOException $e) {
			trigger_error(CRLF . "-> ".$e->getMessage() . CRLF . "-> On Exec SQL: ".$query, E_USER_ERROR);
		}

	}

	/**
	 * 取得一行结果集中某个字段值
	 * @param mixed $sqlOrStmt
	 * @param int $fetchIndex
	 * @return mixed
	 */
	public function fetchField($sqlOrStmt, $fetchIndex = 0)
	{
		if (is_string($sqlOrStmt)) {
			$sqlOrStmt = $this->query($sqlOrStmt);
		}
		$rtn = $sqlOrStmt->fetchColumn($fetchIndex);

		return $rtn === false ? null : $rtn;
	}

	/**
	 * 取得单条记录.
	 * @param mixed $sqlOrStmt
	 * @param int $fetchMode
	 * @return array
	 */
	public function fetchRow($sqlOrStmt, $fetchMode = null)
	{
		if (is_string($sqlOrStmt)) {
			$sqlOrStmt = $this->query($sqlOrStmt);
		}
		$fetchMode = ($fetchMode != null) ? $fetchMode : $this->fetchMode;
		$sqlOrStmt->setFetchMode($fetchMode);
		$rtn = $sqlOrStmt->fetch();

		return $rtn === false ? array() : $rtn;
	}

	/**
	 * 将结果集以键值对的形式储存到数组中.
	 * @param mixed $sqlOrStmt
	 * @param int $fetchMode
	 * @return array
	 */
	public function fetchData($sqlOrStmt, $fetchMode = null)
	{
		if (is_string($sqlOrStmt)) {
			$sqlOrStmt = $this->query($sqlOrStmt);
		}
		$fetchMode = ($fetchMode != null) ? $fetchMode : $this->fetchMode;
		$sqlOrStmt->setFetchMode($fetchMode);
		$rtn = $sqlOrStmt->fetchAll();

		return $rtn === false ? array() : $rtn;
	}

	/**
	 * 过滤数据，剔除数据表中不包含的键值对,并缓存表结构.
	 * 本函数适用于向数据库更新数据.（对字段模版不做检查）.
	 * @param string $tableName 数据表名
	 * @param array  $data  键值对的集合
	 * @param bool  $strictMode 严格模式
	 * @return array 能够更新数据库的键值对子集
	 */
	private function facadeData($tableName, &$data, $strictMode = false)
	{
		if (!is_array($this->tableColumnCache[$tableName])) {
			$this->tableStruct($tableName);
		}
		$facadeSet = array();
		foreach ($data as $key => &$val) {
			if (!is_int($key)) {
				$upcase_key = strtoupper($key);
				if ($columnInfo = $this->tableColumnCache[$tableName][$upcase_key]) {
					if (is_scalar($val) || is_null($val)) {
						$facadeSet[$key] = $this->filterData($val, $columnInfo);
					} elseif ($strictMode == true) {
						trigger_error("字段‘{$key}’的值必须是标量！", E_USER_ERROR);
					}
				} elseif ($strictMode == true) {
					trigger_error("表‘{$tableName}’中不存在字段:‘{$key}’！", E_USER_ERROR);
				}
			} else {
				$facadeSet[$key] = $val;
			}
		}

		return $facadeSet;
	}

	/**
	 * 通过表名获取主键.
	 * @param string $tableName
	 * @param bool   $strictMode
	 * @return mixed
	 */
	public function mapTablePri($tableName, $strictMode = true)
	{
		if (!array_key_exists($tableName, $this->tableColumnCache)) {
			$this->tableStruct($tableName);
		}
		$pri_key = $this->tablePRICache[$tableName];
		if ($strictMode && $pri_key == null) {
			trigger_error("该表‘{$tableName}’没有设主键（映射操作不支持复合主键）");
		}

		return $pri_key;
	}

	/**
	 * 通过映射取得单条记录的某个字段.
	 * @param string $tableName
	 * @param mixed  $reflex    主键值或数组（键值对应字段，值对应字段值）
	 * @param string $field     字段名
	 * @return mixed
	 */
	public function mapField($tableName, $reflex, $field)
	{
		$conditionSet = array();
		if (is_array($reflex)) {
			$conditionSet = &$reflex;
		} else if(is_scalar($reflex) || $reflex === null) {
			$pri_key = $this->mapTablePri($tableName);
			$conditionSet[$pri_key] = $reflex;
		}
		if (preg_match('/^\\{\\^(.+?)\\$\\}$/s', $field, $matches)) {
			$field = $this->dec($matches[1]);
		}else if ($field != null) {
			$field = $this->formatKeyWord($field);
		}else{
			trigger_error('必须指定返回列！', E_USER_ERROR);
		}
		$condition = $this->buildAssocCondition($tableName, $conditionSet);
		$formatedTableName = $this->formatKeyWord($tableName);
		$stmt = $this->query("SELECT {$field} FROM {$formatedTableName} WHERE {$condition} LIMIT 1 OFFSET 0");

		return $this->fetchField($stmt);
	}

	/**
	 * 通过映射取得单条记录.
	 * @param string $tableName
	 * @param mixed  $reflex    主键值或数组（键值对应字段，值对应字段值）
	 * @param mixed  $fieldSet  返回那些字段
	 * @return mixed
	 */
	public function mapRow($tableName, $reflex = array(), $fieldSet = '*')
	{
		$conditionSet = array();
		if (is_array($reflex)) {
			$conditionSet = &$reflex;
		} else if(is_scalar($reflex) || $reflex === null) {
			$pri_key = $this->mapTablePri($tableName);
			$conditionSet[$pri_key] = $reflex;
		}
		if($fieldSet != '*' && !is_array($fieldSet)){
			$fieldSet = array($fieldSet);
		}
		if (is_array($fieldSet)) {
			foreach ($fieldSet as $key => &$field) {
				if (preg_match('/^\\{\\^(.+?)\\$\\}$/s', $field, $matches)) {
					$fieldSet[$key] = $this->dec($matches[1]);
				} else {
					$fieldSet[$key] = $this->formatKeyWord($field);
				}
			}
			$fieldSet = implode(', ', $fieldSet);
		}
		$condition = $this->buildAssocCondition($tableName, $conditionSet);
		$formatedTableName = $this->formatKeyWord($tableName);
		$stmt = $this->query("SELECT {$fieldSet} FROM {$formatedTableName} WHERE {$condition} LIMIT 1 OFFSET 0");

		return $this->fetchRow($stmt);
	}

	/**
	 * 通过映射取得符合条件的所有数据.
	 * @param string $tableName
	 * @param mixed  $reflex    主键值或数组（键值对应字段，值对应字段值）
	 * @param string $field     字段名
	 * @return mixed
	 */
	public function mapData($tableName, $reflex = array(), $fieldSet = '*')
	{

		$conditionSet = array();
		if (is_array($reflex)) {
			$conditionSet = &$reflex;
		} else if(is_scalar($reflex) || $reflex === null) {
			$pri_key = $this->mapTablePri($tableName);
			$conditionSet[$pri_key] = $reflex;
		}
		if($fieldSet != '*' && !is_array($fieldSet)){
			$fieldSet = array($fieldSet);
		}
		if (is_array($fieldSet)) {
			foreach ($fieldSet as $key => &$field) {
				if (preg_match('/^\\{\\^(.+?)\\$\\}$/s', $field, $matches)) {
					$fieldSet[$key] = $this->dec($matches[1]);
				} else {
					$fieldSet[$key] = $this->formatKeyWord($field);
				}
			}
			$fieldSet = implode(', ', $fieldSet);
		}
		$formatedTableName = $this->formatKeyWord($tableName);
		if (count($conditionSet) == 0) {
			return $this->fetchData("SELECT {$fieldSet} FROM {$formatedTableName}");
		} else {
			$condition = $this->buildAssocCondition($tableName, $conditionSet);
			$stmt = $this->query("SELECT {$fieldSet} FROM {$formatedTableName} WHERE {$condition}");

			return $this->fetchData($stmt);
		}
	}

	/**
	 * 语法糖，求取指定条件的记录数量.
	 * @param string $tableName
	 * @param mixed  $reflex 主键值或数组
	 * @return mixed
	 */
	public function mapCount($tableName, $reflex = array())
	{

		return $this->mapField($tableName, $reflex, $this->enc('count(*)'));
	}

	/**
	 * 返回数据表的列信息
	 * 仅获取列名的话可对返回结果进行如下操作：
	 * $simpleColumns = $func->arrayColumn($ret, 'Field');
	 * @param string $tableName
	 * @return array
	 */
	public function mapColumns($tableName)
	{
		if (!is_array($this->tableColumnCache[$tableName])) {
			$this->tableStruct($tableName);
		}

		return $this->tableColumnCache[$tableName];
	}

	/**
	 * 通过映射主键表删除记录.
	 * @param string $tableName
	 * @param mixed  $reflex    主键值或数组（键值对应字段，值对应字段值）
	 * @return mixed
	 */
	public function mapDelete($tableName, $reflex = array())
	{
		$conditionSet = array();
		if (is_array($reflex)) {
			$conditionSet = &$reflex;
		} else if(is_scalar($reflex) || $reflex === null) {
			$pri_key = $this->mapTablePri($tableName);
			$conditionSet[$pri_key] = $reflex;
		}
		if(count($conditionSet) == 0){
			trigger_error('删除失败，没有条件的操作很危险！', E_USER_ERROR);
		}
		$formatedTableName = $this->formatKeyWord($tableName);
		$sql = 'DELETE FROM ' . $formatedTableName . ' WHERE ';
		$condition = $this->buildAssocCondition($tableName, $conditionSet);

		return $this->exec($sql . $condition);
	}

	/**
	 * 通过映射主键表更新记录.
	 * @param string $tableName
	 * @param mixed  $reflex    主键值或数组（键值对应字段，值对应字段值）
	 * @param array  $data      更新数据
	 * @return int
	 */
	public function mapUpdate($tableName, $reflex = array(), $data)
	{
		$conditionSet = array();
		if (is_array($reflex)) {
			$conditionSet = &$reflex;
		} else if(is_scalar($reflex) || $reflex === null) {
			$pri_key = $this->mapTablePri($tableName);
			$conditionSet[$pri_key] = $reflex;
		}
		$data = $this->facadeData($tableName, $data);
		if (count($data) == 0) {
			trigger_error('错误，没有可更新的数据！', E_USER_ERROR);
		}
		if(count($conditionSet) == 0){
			trigger_error('更新失败，没有条件的操作很危险！', E_USER_ERROR);
		}
		$formatedTableName = $this->formatKeyWord($tableName);
		$sql = 'UPDATE '.$formatedTableName.' SET ';
		foreach ($data as $set_key => &$val) {
			if (is_int($set_key)) {
				$sql .= $val . ', ';
			} else {
				$sql .= " {$this->formatKeyWord($set_key)} = {$this->enc($val)}, ";
			}
		}
		$sql = substr($sql, 0, strlen($sql) - 2).' WHERE ';
		$condition = $this->buildAssocCondition($tableName, $conditionSet);

		return $this->exec($sql . $condition);
	}

	/**
	 * 过滤并向数据库插入数据.
	 * @param string $tableName 数据表名
	 * @param array  $data  键值对的集合,通常来自表单
	 * @return bool
	 */
	public function postData($tableName, $data)
	{
		$data = $this->facadeData($tableName, $data);
		if (count($data) == 0) {
			trigger_error('错误，没有可插入的数据！', E_USER_ERROR);
		}
		$fieldStr = '';
		$valueStr = '';
		foreach ($data as $key => &$val) {
			$fieldStr .= ', ' . $this->formatKeyWord($key);
			$valueStr .= ', ' . $this->enc($val);
		}
		$fieldStr = substr($fieldStr, 2);
		$valueStr = substr($valueStr, 2);
		$formatedTableName = $this->formatKeyWord($tableName);
		$sql = 'INSERT INTO '.$formatedTableName.' ('.$fieldStr.') VALUES ('.$valueStr.')';
		$result = $this->exec($sql);
		if($result !== false){

			return $this->lastInsertId();
		}

		return 0;
	}

	/**
	 * 绑定数据到预处理句柄.
	 * @param object $stmt PDOStatement
	 * @param array  $paramSet  键值对的集合,通常来自表单
	 * @return void
	 */
	private function bindAssocArrayValue($stmt, &$paramSet)
	{
		if (is_object($stmt) && ($stmt instanceof PDOStatement)) {
			foreach ($paramSet as $key => &$value) {
				if (is_int($value)) {
					$param = PDO::PARAM_INT;
				} elseif (is_bool($value)) {
					$param = PDO::PARAM_BOOL;
				} elseif (is_null($value)) {
					$param = PDO::PARAM_NULL;
				} elseif (is_string($value) || is_float($value)) {
					$param = PDO::PARAM_STR;
				} else {
					trigger_error('Invalid data type!', E_USER_ERROR);
				}
				$stmt->bindValue(':'.$key, $value, $param);
			}
		}
	}

	/**
	 * 构造键值对关联的预查询条件.
	 * @param string $table
	 * @param array $data
	 * @return string
	 */
	function buildAssocCondition($tableName, &$data)
	{
		$conditionStr = '1 = 1';
		if (!is_array($this->tableColumnCache[$tableName])) {
			$this->tableStruct($tableName);
		}
		foreach ($data as $key => &$val) {
			if(!is_scalar($val) && $val !== null){
				trigger_error("条件的值‘{$val}’必须是标量！", E_USER_ERROR);
			}
			if (!is_int($key)) {
				$upcase_key = strtoupper($key);
				$columnInfo = $this->tableColumnCache[$tableName][$upcase_key];
				if ($columnInfo !== null) {
					$format_key = $this->formatKeyWord($key);
					$conditionStr .= ' AND ' . $format_key . ' = ' . $this->enc($val);
				} else {
					trigger_error("不存在该字段:‘{$key}’！", E_USER_ERROR);
				}
			} else {
				// 简单的安全检测，防止书写错误
				if (!preg_match('/[\\(\\)><=\\s]+/s', trim($val))) {
					trigger_error("值‘{$val}’好像不是有效的表达式！", E_USER_ERROR);
				}
				$conditionStr .= ' AND (' . $val . ')';
			}
		}

		return $conditionStr;
	}

	/**
	 * 获取拼接的 INSERT 语句.
	 * @param string $tableName 表名
	 * @param array  $data  数据
	 * @return string sql
	 */
	public function getInsertSql($tableName, $data)
	{
		$values = array();
		$fields = array();
		$data = $this->facadeData($tableName, $data);
		foreach ($data as $key => &$val) {
			if ($val !== null) {
				$values[] = $this->quote($val);
			} else {
				$values[] = 'NULL';
			}
			$fields[] = $this->formatKeyWord($key);
		}
		$tableName = $this->formatKeyWord($tableName);
		$sql = 'INSERT INTO '.$tableName.'('.implode(', ', $fields).')VALUES('.implode(', ', $values).')';

		return $sql;
	}

	/**
	 * 获取拼接的 UPDATE 语句.
	 * @param string $table 表名
	 * @param array  $data  数据
	 * @param mixed  $conditionSet 条件
	 * @return string sql
	 */
	public function getUpdateSql($tableName, $data, $conditionSet = null)
	{
		$sql = 'UPDATE '.$this->formatKeyWord($tableName).' SET ';
		$data = $this->facadeData($tableName, $data);
		foreach ($data as $key => &$val) {
			$val = $this->quote($val);
			$sql .= $this->formatKeyWord($key) . ' = ' . $val . ', ';
		}
		$sql = substr($sql, 0, strlen($sql) - 2).' WHERE ';
		if ($conditionSet != null && is_array($conditionSet)) {
			$condition = $this->buildAssocCondition($tableName, $conditionSet);
			$sql = $sql . ' ' . $condition;
		} elseif ($conditionSet != null) {
			$sql = $sql . ' ' . $condition;
		}

		return $sql;
	}

	/**
	 * SELECT查询记录总数
	 * @param string $query 不能含有Group By的子句
	 * @return int
	 */
	public function resultCount($query)
	{
		$regex = '/select((?:(?R)|(?!select|from).)*)from/si';
		if (preg_match($regex, $query, $output) === 1) {
			if(strpos($query, $output[0]) != 0){
				trigger_error('Invalid select sql!', E_USER_ERROR);
			}
			$pos = strpos($query, $output[1]);
			$query = substr_replace($query, ' count(*) ', $pos, strlen($output[1]));
			$stmt = $this->query($query);

			return (int) $stmt->fetchColumn();
		}

		trigger_error('Invalid select sql!', E_USER_ERROR);
	}

	/**
	 * 求取指定数据表的字段注释
	 * @param  string $tableName
	 * @return array
	 */
	public function mapComments($tableName)
	{
		$DBName = $this->getDBName();
		$stmt = $this->query("SELECT `column_name`, `column_comment` FROM `information_schema`.`columns` WHERE `table_schema` = '{$DBName}' AND `table_name` = {$this->enc($tableName)}");
		$retData = array();
		while ($info = $stmt->fetch()) {
			$key = $info['column_name'];
			$val = ($info['column_comment'] == '')? $key : $info['column_comment'];
			$retData[$key] = $val;
		}

		return $retData;
	}

	/**
	 * 遍历数据库中的表，并对其进行碎片优化
	 * @param  integer $size 碎片数量
	 * @return void
	 */
	public function optimize($minSize = 0){
		$DBName = $this->getDBName();
		$query = "select table_name from information_schema.tables where table_schema='{$DBName}' and data_free > '{$minSize}'";
		$stmt = $this->query($query);
		while($info = $stmt->fetch()){
			$this->exec("optimize table {$info['table_name']}");
		}
	}

	/**
	 * 过滤并向数据库插入数据，如果主键或唯一索引冲突则更新数据.
	 * @param string $tableName 数据表名
	 * @param array  $data  键值对的集合,通常来自表单
	 * @param array  $updateFields  如果冲突更新哪些字段
	 * @return mixed
	 */
	public function insertOnDupUpdate($tableName, $data, $updateFields = "*"){
		$data = $this->facadeData($tableName, $data);
		$columns = array_keys($data);
		if (count($data) == 0) {
			trigger_error('没有可插入的数据！', E_USER_ERROR);
		}
		// 检查唯一索引列是否存在
		$found_uni_key= null;
		foreach($this->tableUNICache[$tableName] as $uni_key){
			if(array_key_exists($uni_key, $data)){
				$found_uni_key = $uni_key;
				break;
			}
		}
		if($found_uni_key == null){
			trigger_error('数据必须含有唯一索引列！', E_USER_ERROR);
		}
		$fieldStr = '';
		$valueStr = '';
		foreach ($data as $key => &$val) {
			$fieldStr .= ', '.$this->formatKeyWord($key);
			$valueStr .= ', '.$this->enc($val);
		}
		$fieldStr = substr($fieldStr, 1);
		$valueStr = substr($valueStr, 1);
		$formatedTableName = $this->formatKeyWord($tableName);
		$sql = 'INSERT INTO '.$formatedTableName.' ('.$fieldStr.') VALUES ('.$valueStr.') ON DUPLICATE KEY UPDATE';
		if($updateFields == '*'){
			$updateFields = $columns;
		}
		foreach($updateFields as $key){
			if(!in_array($key, $columns)){
				trigger_error('字段：'.$key.'不存在！', E_USER_ERROR);
			}
			$formated_key = $this->formatKeyWord($key);
			$sql .=" {$formated_key} = VALUES({$formated_key}), ";
		}
		$sql = substr($sql, 0, strlen($sql) - 2);

		return $this->exec($sql);
	}
}

/*
### 初始化PDO并将可能出现的异常转换为错误输出
```
// initialize an instance
try{
	$pdo= new PDOMySQLDriver();
}catch(PDOException $e){
	trigger_error($e->getMessage(), E_USER_ERROR);
}
```
*/